/*
 * Created on 2007-4-16
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package dao;

import java.sql.*;
import java.util.*;

import javax.swing.JOptionPane;

import entity.Payment;
import entity.WebUser;


/**
 * @author 
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class WebUserDAO {
    private Connection conn = null;
    private Statement st = null;
    public WebUserDAO() {
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager
                    .getConnection(
                            "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Contract","sa","123456");
            st = conn.createStatement();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public synchronized void release() {
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }

    }

    public synchronized List loadAllUsers() throws Exception{
        List result = new ArrayList();
        try {
            ResultSet rs = st.executeQuery("select * from Users");
            while (rs.next()) {
                WebUser user=new WebUser();
                user.setPwd(rs.getString("password"));
                user.setUserno(rs.getString("userno"));
                user.setDepartment(rs.getString("department"));
                user.setUsertype(rs.getString("usertype"));
                result.add(user);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("数据库查询错误");
        }
        return result;
    }
    
    public WebUser getWebUserByCno(String userno) {
        try {
            String sql = "select * from Users where userno='"+userno+"';"; // SQL语句
            ResultSet rs = st.executeQuery(sql);
            if (rs.next()) {
            	 WebUser user=new WebUser();
                 user.setPwd(rs.getString("password"));
                 user.setUserno(rs.getString("userno"));
                 user.setDepartment(rs.getString("department"));
                 user.setUsertype(rs.getString("usertype"));
                return user;
            }else {
                return null;
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }

    }

    
    public synchronized void addUser(WebUser user) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from Users where userno='"+user.getUserno()+"'");
            if (rs.next()) {
                throw new Exception("用户编号不能重复");
            }
            String sql="insert into Users(userno,password,usertype,department) values('"+user.getUserno()+"','"
            +user.getPwd()+"','"+user.getUsertype()+"','"+user.getDepartment()+"')";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("数据库错误");
        }
        
    }
    public synchronized void saveUser(WebUser user) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from Users where userno='"+user.getUserno()+"'");
            if (!rs.next()) {
                throw new Exception("用户不存在");
            }
            String sql="update Users set password='"+user.getPwd()
                    +"',usertype='"+user.getUsertype()+"',department='"
                    +user.getDepartment()+"' where userno='"+user.getUserno()+"'";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("数据库错误");
        }
        
    }
    public synchronized void delUser(String userno) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from Users where userno='"+userno+"'");
            if (!rs.next()) {
                throw new Exception("用户不存在");
            }
            String sql="delete from Users where userno='"+userno+"'";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("数据库错误");
        }
    }
    public synchronized void Userpass_edit(String userno,String newpassword) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from Users where userno='"+userno+"'");
            if (!rs.next()) {
                throw new Exception("用户不存在");
            }
            String sql="update Users set password='"+newpassword+"' where userno='"+userno+"'";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("数据库错误");
        }    
    }
    public synchronized WebUser readUser(String userno) throws Exception{
        WebUser result=null;
        try {
            ResultSet rs = st.executeQuery("select * from Users where userno='"+userno+"'");
            if (rs.next()) {
                result=new WebUser();
                result.setPwd(rs.getString("password"));
                result.setUserno(rs.getString("userno"));
                result.setDepartment(rs.getString("department"));
                result.setUsertype(rs.getString("usertype"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("数据库查询错误");
        }
        
        return result;
        
    }
    public synchronized String getType(String userno) throws Exception{
    	String usertype=null;
        try {
            ResultSet rs = st.executeQuery("select * from users where userno='"+userno+"'");
            if (rs.next()) {
            	usertype=(rs.getString("usertype"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("数据库查询错误");
        }
        
        return usertype;
        
    }

}
